using AutoMapper;
using ClosedXML.Excel;
using MediatR;
using RBAC.Domain.MaterialManage;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Write.Api.Application.Command.ERP.MaterialFile;
using System.Data;
using System.Text;

namespace RBAC.Write.Api.Application.Handler.ERP.MaterialFile
{
    /// <summary>
    /// 导出物料基本档案处理器
    /// </summary>
    public class ExportMaterialHandler : IRequestHandler<ExportMaterialCommand, APIResult<byte[]>>
    {
        private readonly IBaseRepository<Mater> materRepository;
        private readonly IBaseRepository<MaterType> materTypeRepository;
        private readonly ILogger<ExportMaterialHandler> logger;
        private readonly IMapper mapper;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="materRepository">物料仓储接口</param>
        /// <param name="materTypeRepository">物料分类仓储接口</param>
        /// <param name="logger">日志记录器</param>
        /// <param name="mapper">对象映射器</param>
        public ExportMaterialHandler(
            IBaseRepository<Mater> materRepository,
            IBaseRepository<MaterType> materTypeRepository,
            ILogger<ExportMaterialHandler> logger,
            IMapper mapper)
        {
            this.materRepository = materRepository;
            this.materTypeRepository = materTypeRepository;
            this.logger = logger;
            this.mapper = mapper;
        }

        /// <summary>
        /// 处理导出物料基本档案请求
        /// </summary>
        /// <param name="request">导出请求</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>Excel文件字节数组</returns>
        public Task<APIResult<byte[]>> Handle(ExportMaterialCommand request, CancellationToken cancellationToken)
        {
            APIResult<byte[]> result = new APIResult<byte[]>();

            try
            {
                logger.LogInformation("开始导出物料基本档案数据，查询条件：MaterialCode={MaterialCode}, MaterialName={MaterialName}, TypeId={TypeId}, Status={Status}",
                    request.MaterialCode, request.MaterialName, request.TypeId, request.Status);

                // 构建查询条件
                var whereClause = new StringBuilder("m.IsDeleted = 0");
                var parameters = new Dictionary<string, object>();

                // 物料编码模糊查询
                if (!string.IsNullOrWhiteSpace(request.MaterialCode))
                {
                    whereClause.Append(" AND m.MaterCode LIKE @MaterialCode");
                    parameters.Add("MaterialCode", $"%{request.MaterialCode.Trim()}%");
                }

                // 物料名称模糊查询
                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    whereClause.Append(" AND m.MaterName LIKE @MaterialName");
                    parameters.Add("MaterialName", $"%{request.MaterialName.Trim()}%");
                }

                // 物料分类筛选
                if (request.TypeId.HasValue && request.TypeId.Value > 0)
                {
                    // 获取该分类及其所有子分类的ID列表
                    var typeIds = GetAllChildTypeIds(request.TypeId.Value);
                    if (typeIds.Count > 1)
                    {
                        var typeIdsParam = string.Join(",", typeIds);
                        whereClause.Append($" AND m.MaterTypeId IN ({typeIdsParam})");
                    }
                    else
                    {
                        whereClause.Append(" AND m.MaterTypeId = @TypeId");
                        parameters.Add("TypeId", request.TypeId.Value);
                    }
                }

                // 状态筛选
                if (request.Status.HasValue)
                {
                    whereClause.Append(" AND m.Status = @Status");
                    parameters.Add("Status", request.Status.Value);
                }

                // 查询数据
                var sql = $@"
                    SELECT 
                        m.Id,
                        m.MaterCode AS 物料编码,
                        m.MaterName AS 物料名称,
                        mt.TypeName AS 物料分类,
                        m.ModelSpec AS 规格型号,
                        m.SpecDesc AS 规格描述,
                        m.Unit AS 计量单位,
                        CASE m.SupplyMode 
                            WHEN '采购' THEN '采购' 
                            WHEN '自制' THEN '自制' 
                            WHEN '外协' THEN '外协' 
                            ELSE m.SupplyMode 
                        END AS 供应方式,
                        m.DrawingNo AS 图号,
                        CASE m.Status 
                            WHEN 1 THEN '启用' 
                            WHEN 0 THEN '禁用' 
                            ELSE '未知' 
                        END AS 状态,
                        m.CreateTime AS 创建时间,
                        m.UpdatedTimes AS 更新时间,
                        m.CreateUser AS 创建人
                    FROM Mater m
                    LEFT JOIN MaterType mt ON m.MaterTypeId = mt.Id
                    WHERE {whereClause}
                    ORDER BY m.CreateTime DESC";

                var data = materRepository.Query<MaterialExportDto>(sql, parameters)?.ToList() ?? new List<MaterialExportDto>();

                if (!data.Any())
                {
                    result.Code = APIEnums.Error;
                    result.Msg = "没有可导出的物料基本档案数据";
                    return Task.FromResult(result);
                }

                logger.LogInformation("共查询到{Count}条物料基本档案数据", data.Count);

                // 创建DataTable
                DataTable dt = new DataTable("物料基本档案");
                dt.Columns.Add("物料编码", typeof(string));
                dt.Columns.Add("物料名称", typeof(string));
                dt.Columns.Add("物料分类", typeof(string));
                dt.Columns.Add("规格型号", typeof(string));
                dt.Columns.Add("规格描述", typeof(string));
                dt.Columns.Add("计量单位", typeof(string));
                dt.Columns.Add("供应方式", typeof(string));
                dt.Columns.Add("图号", typeof(string));
                dt.Columns.Add("状态", typeof(string));
                dt.Columns.Add("创建时间", typeof(string));
                dt.Columns.Add("更新时间", typeof(string));
                dt.Columns.Add("创建人", typeof(string));

                // 填充数据
                foreach (var item in data)
                {
                    DataRow row = dt.NewRow();
                    row["物料编码"] = item.物料编码 ?? "";
                    row["物料名称"] = item.物料名称 ?? "";
                    row["物料分类"] = item.物料分类 ?? "";
                    row["规格型号"] = item.规格型号 ?? "";
                    row["规格描述"] = item.规格描述 ?? "";
                    row["计量单位"] = item.计量单位 ?? "";
                    row["供应方式"] = item.供应方式 ?? "";
                    row["图号"] = item.图号 ?? "";
                    row["状态"] = item.状态 ?? "";
                    row["创建时间"] = item.创建时间?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
                    row["更新时间"] = item.更新时间?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
                    row["创建人"] = item.创建人 ?? "";
                    dt.Rows.Add(row);
                }

                // 导出为Excel
                byte[] fileBytes = ExportToExcel(dt);

                result.Code = APIEnums.Success;
                result.Msg = "物料基本档案数据导出成功";
                result.Data = fileBytes;

                logger.LogInformation("物料基本档案数据导出成功，共导出{Count}条记录", data.Count);
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "物料基本档案数据导出失败：{Message}", ex.Message);
                result.Code = APIEnums.Error;
                result.Msg = "导出物料基本档案数据失败：" + ex.Message;
            }

            return Task.FromResult(result);
        }

        /// <summary>
        /// 获取指定分类及其所有子分类的ID列表
        /// </summary>
        /// <param name="typeId">分类ID</param>
        /// <returns>分类ID列表</returns>
        private List<long> GetAllChildTypeIds(long typeId)
        {
            var typeIds = new List<long> { typeId };
            var sql = "SELECT Id, ParentId FROM MaterType WHERE IsDeleted = 0";
            var allTypes = materTypeRepository.Query<(long Id, long? ParentId)>(sql).ToList();

            // 递归获取所有子分类ID
            GetChildIds(allTypes, typeId, typeIds);

            return typeIds;
        }

        /// <summary>
        /// 递归获取子分类ID
        /// </summary>
        /// <param name="allTypes">所有分类数据</param>
        /// <param name="parentId">父级ID</param>
        /// <param name="result">结果列表</param>
        private void GetChildIds(List<(long Id, long? ParentId)> allTypes, long parentId, List<long> result)
        {
            var children = allTypes.Where(t => t.ParentId == parentId).ToList();
            foreach (var child in children)
            {
                result.Add(child.Id);
                GetChildIds(allTypes, child.Id, result);
            }
        }

        /// <summary>
        /// 将DataTable导出为Excel字节数组
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <returns>Excel文件字节数组</returns>
        private byte[] ExportToExcel(DataTable dataTable)
        {
            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add(dataTable);

                // 设置表头样式
                var headerRow = worksheet.Row(1);
                headerRow.Style.Font.Bold = true;
                headerRow.Style.Font.FontColor = XLColor.White;
                headerRow.Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
                headerRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

                // 自动调整列宽
                worksheet.Columns().AdjustToContents();

                // 设置边框
                worksheet.RangeUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                worksheet.RangeUsed().Style.Border.InsideBorder = XLBorderStyleValues.Thin;

                // 设置数据格式
                for (int i = 2; i <= worksheet.RowsUsed().Count(); i++)
                {
                    var row = worksheet.Row(i);
                    row.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                }

                using (var memoryStream = new MemoryStream())
                {
                    workbook.SaveAs(memoryStream);
                    return memoryStream.ToArray();
                }
            }
        }
    }

    /// <summary>
    /// 物料基本档案导出DTO
    /// </summary>
    public class MaterialExportDto
    {
        public long Id { get; set; }
        public string? 物料编码 { get; set; }
        public string? 物料名称 { get; set; }
        public string? 物料分类 { get; set; }
        public string? 规格型号 { get; set; }
        public string? 规格描述 { get; set; }
        public string? 计量单位 { get; set; }
        public string? 供应方式 { get; set; }
        public string? 图号 { get; set; }
        public string? 状态 { get; set; }
        public DateTime? 创建时间 { get; set; }
        public DateTime? 更新时间 { get; set; }
        public string? 创建人 { get; set; }
    }
}